﻿Imports System.Data.SqlClient
Imports System.Data
Public Class DangKyDAO
    'Public Function GetItemID(ByVal masinhvien As String) As DataTable
    '    Dim cnn As New SqlConnection
    '    Dim dataProvider As New DataProvider
    '    Dim da As SqlDataAdapter
    '    Dim strCon As String = "select dt.MaDangKy, dt.MaSinhVien, dt.TieuDe,ln.TenLoaiNha, kv.TenKhuVuc, dt.GiaThue, dt.DienTich, dt.SucChua, dt.TrangThai, dt.ThoiGianDangKy, dt.ThoiGianDong, dt.OGhep, dt.OChungChu,dt.GanCho,dt.GanTramXeBuyt,dt.ChoNuThue,dt.ChoNamThue,dt.ChoNauAn,dt.Internet,dt.TruyenHinhCap from DANGKY dt,LOAINHA ln, KHUVUC kv where dt.MaLoaiNha = ln.MaLoaiNha and dt.MaKhuVucThue = kv.MaKhuVuc and dt.MaSinhVien = '" + masinhvien + "'"
    '    cnn = dataProvider.ConnectionData()
    '    da = New SqlDataAdapter(strCon, cnn)
    '    Dim data As New DataTable
    '    da.Fill(data)
    '    Return data
    'End Function
    Public Function GetItemID(ByVal masinhvien As String) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from DANGKY where MaSinhVien = '" + masinhvien + "'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function GetItemTrangThai(ByVal trangthaiDK As Integer) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from DANGKY where TrangThai = " + trangthaiDK.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    'Public Function GetItemID(ByVal masinhvien As String) As DangKyDTO
    '    Dim dkDTO As New DangKyDTO
    '    Dim cnn As New SqlConnection
    '    Dim dataProvider As New DataProvider
    '    Dim da As SqlDataAdapter
    '    Dim strCon As String = "select dt.MaDangKy, dt.MaSinhVien, ln.TenLoaiNha, kv.TenKhuVuc, dt.GiaThue, dt.DienTich, dt.SucChua, dt.TrangThai, dt.ThoiGianDangKy, dt.ThoiGianDong, dt.OGhep, dt.OChungChu,dt.GanCho,dt.GanTramXeBuyt,dt.ChoNuThue,dt.ChoNamThue,dt.ChoNauAn,dt.Internet,dt.TruyenHinhCap from DANGKY dt,LOAINHA ln, KHUVUC kv where dt.MaLoaiNha = ln.MaLoaiNha and dt.MaKhuVucThue = kv.MaKhuVuc and dt.MaSinhVien = '" + masinhvien + "'"
    '    cnn = dataProvider.ConnectionData()
    '    da = New SqlDataAdapter(strCon, cnn)
    '    Dim data As New DataTable
    '    da.Fill(data)

    '    Return dkDTO
    'End Function
    Public Function GetAll() As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from DANGKY"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Sub Insert(ByVal dk As DangKyDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_InsertDANGKY @TieuDe,@MaSinhVien,@MaKhuVucThue,@MaLoaiNha,@GiaThue,@DienTich,@SucChua,@ThoiGianDangTin,@ThoiGianDongTin,@OGhep,@OChungChu,@GanCho,@GanTramXeBuyt,@ChoNuThue,@ChoNamThue,@ChoNauAn,@Internet,@TruyenHinhCap,@TrangThai,@MaDangKy"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaSinhVien", SqlDbType.Int)
            para.Value = dk.MaSinhVien
            para = cmd.Parameters.Add("@TieuDe", SqlDbType.NVarChar)
            para.Value = dk.TieuDe
            para = cmd.Parameters.Add("@MaKhuVucThue", SqlDbType.Int)
            para.Value = dk.MaKhuVucThue
            para = cmd.Parameters.Add("@MaLoaiNha", SqlDbType.Int)
            para.Value = dk.MaLoaiNha
            para = cmd.Parameters.Add("@GiaThue", SqlDbType.Int)
            para.Value = dk.GiaThue
            para = cmd.Parameters.Add("@DienTich", SqlDbType.Float)
            para.Value = dk.DienTich
            para = cmd.Parameters.Add("@SucChua", SqlDbType.Int)
            para.Value = dk.SucChua
            para = cmd.Parameters.Add("@ThoiGianDangTin", SqlDbType.VarChar)
            para.Value = dk.ThoiGianDangKy
            para = cmd.Parameters.Add("@ThoiGianDongTin", SqlDbType.VarChar)
            para.Value = dk.ThoiGianDong
            para = cmd.Parameters.Add("@OGhep", SqlDbType.Int)
            para.Value = dk.OGhep
            para = cmd.Parameters.Add("@OChungChu", SqlDbType.Int)
            para.Value = dk.OChungChu
            para = cmd.Parameters.Add("@GanCho", SqlDbType.Int)
            para.Value = dk.GanCho
            para = cmd.Parameters.Add("@GanTramXeBuyt", SqlDbType.Int)
            para.Value = dk.GanTramXeBuyt
            para = cmd.Parameters.Add("@ChoNuThue", SqlDbType.Int)
            para.Value = dk.ChoNuThue
            para = cmd.Parameters.Add("@ChoNamThue", SqlDbType.Int)
            para.Value = dk.ChoNamThue
            para = cmd.Parameters.Add("@ChoNauAn", SqlDbType.Int)
            para.Value = dk.ChoNauAn
            para = cmd.Parameters.Add("@Internet", SqlDbType.Int)
            para.Value = dk.Internet
            para = cmd.Parameters.Add("@TruyenHinhCap", SqlDbType.Int)
            para.Value = dk.TruyenHinhCap
            para = cmd.Parameters.Add("@TrangThai", SqlDbType.Int)
            para.Value = dk.TrangThai
            para = cmd.Parameters.Add("@MaDangKy", SqlDbType.Int)
            para.Value = dk.MaDangKy
            cmd.ExecuteNonQuery()
            MessageBox.Show("Đăng ký thành công, Vui lòng chờ xét duyệt", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Sub Delete(ByVal dk As DangKyDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_DeleteDANGKY @MaDangKy"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaDangKy", SqlDbType.Int)
            para.Value = dk.MaDangKy
            cmd.ExecuteNonQuery()
            MessageBox.Show("Đã xóa mẫu đăng ký thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Sub Update(ByVal dk As DangKyDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_UpdateDANGKY @MaDangKy,@TieuDe,@MaSinhVien,@MaKhuVucThue,@MaLoaiNha,@GiaThue,@DienTich,@SucChua,@OGhep,@OChungChu,@GanCho,@GanTramXeBuyt,@ChoNuThue,@ChoNamThue,@ChoNauAn,@Internet,@TruyenHinhCap,@TrangThai"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaDangKy", SqlDbType.Int)
            para.Value = dk.MaDangKy
            para = cmd.Parameters.Add("@TieuDe", SqlDbType.NVarChar)
            para.Value = dk.TieuDe
            para = cmd.Parameters.Add("@MaSinhVien", SqlDbType.Int)
            para.Value = dk.MaSinhVien
            para = cmd.Parameters.Add("@MaKhuVucThue", SqlDbType.Int)
            para.Value = dk.MaKhuVucThue
            para = cmd.Parameters.Add("@MaLoaiNha", SqlDbType.Int)
            para.Value = dk.MaLoaiNha
            para = cmd.Parameters.Add("@GiaThue", SqlDbType.Int)
            para.Value = dk.GiaThue
            para = cmd.Parameters.Add("@DienTich", SqlDbType.Float)
            para.Value = dk.DienTich
            para = cmd.Parameters.Add("@SucChua", SqlDbType.Int)
            para.Value = dk.SucChua
            'para = cmd.Parameters.Add("@ThoiGianDangTin", SqlDbType.VarChar)
            'para.Value = dk.ThoiGianDangKy
            'para = cmd.Parameters.Add("@ThoiGianDongTin", SqlDbType.VarChar)
            'para.Value = dk.ThoiGianDong
            para = cmd.Parameters.Add("@OGhep", SqlDbType.Int)
            para.Value = dk.OGhep
            para = cmd.Parameters.Add("@OChungChu", SqlDbType.Int)
            para.Value = dk.OChungChu
            para = cmd.Parameters.Add("@GanCho", SqlDbType.Int)
            para.Value = dk.GanCho
            para = cmd.Parameters.Add("@GanTramXeBuyt", SqlDbType.Int)
            para.Value = dk.GanTramXeBuyt
            para = cmd.Parameters.Add("@ChoNuThue", SqlDbType.Int)
            para.Value = dk.ChoNuThue
            para = cmd.Parameters.Add("@ChoNamThue", SqlDbType.Int)
            para.Value = dk.ChoNamThue
            para = cmd.Parameters.Add("@ChoNauAn", SqlDbType.Int)
            para.Value = dk.ChoNauAn
            para = cmd.Parameters.Add("@Internet", SqlDbType.Int)
            para.Value = dk.Internet
            para = cmd.Parameters.Add("@TruyenHinhCap", SqlDbType.Int)
            para.Value = dk.TruyenHinhCap
            para = cmd.Parameters.Add("@TrangThai", SqlDbType.Int)
            para.Value = dk.TrangThai
            cmd.ExecuteNonQuery()
            MessageBox.Show("Cập nhật thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class
